

macs = {}

f = open('wireless-kwan.csv', mode='ra')
for line in f:
    # name,ip address,cname,mac - wired,wireless - a,wireless - b,notes
    name, ip, cname, wired_mac, wireless_a, wireless_b, notes = line.split(',')
    wireless_a = wireless_a.strip()
    wireless_b = wireless_b.strip()
    cname = cname.strip()
    
    if wireless_a not in ['n/a', '']:
        macs[wireless_a] = cname
    if wireless_b not in ['n/a', '']:
        macs[wireless_b] = cname
    
f.close()

import re
r = re.compile('.*-(\d+)')

import sqlite3
conn = sqlite3.connect('aps.db')
c = conn.cursor()
c.execute(''' create table aps (mac string, room string) ''')
conn.commit()

for mac, name in macs.iteritems():
    room = str(r.match(name).groups(0)[0])
    c = conn.cursor()
    c.execute(''' insert into aps values (?,?) ''', (mac, room))
    conn.commit()
    
# Sanity check
c = conn.cursor()
c.execute (''' select * from aps ''')
for row in c:
    print row
    